"""
Case Type   : 基础功能-SQL语法-DDL-partition
Case Name   : Hash分区表执行rename partition，并行跨分区delete
Create At   : 2022/12/08
Owner       : kyeleze
Description :
    1 创建hash分区表，并插入数据
    2 第一个session开始事务rename partition
    3 第二个session开始事务跨分区delete
    4 每个session分别执行commit
    5 查询数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.查询数据插入成功
History     :
"""
import os
import unittest

import py_opengauss
from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class HashPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_hash_table_0084'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '--step1：创建hash分区表，并插入数据 expect:成功--'
        self.log.info(step)
        sql_cmd = f'''drop table if exists {self.table_name};
                   create table {self.table_name} (col1 int, col2 int)
                   partition by hash(col1)
                   (
                   partition p1,
                   partition p2
                   );'''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        sql_cmd = f'insert into {self.table_name} values(1,1),(3,3);'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      result, "执行失败:" + step)

        step = '--step2：第一个session开始事务rename partition expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        ddl_result = self.db1.query(f'alter table {self.table_name} '
                                    f'rename partition p1 to p3;')
        self.log.info(ddl_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.assertIn(self.constant.ALTER_TABLE_MSG,
                      ddl_result, "执行失败:" + step)

        step = '--step3：第二个session开始事务跨分区delete expect:成功--'
        self.log.info(step)
        self.db2 = py_opengauss.open(self.con_info)
        begin_result = self.db2.query('begin;')
        self.log.info(begin_result)
        dml_result = self.db2.query(f'delete from {self.table_name} where '
                                    f'col1=1;')
        self.log.info(dml_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG,
                      dml_result, "执行失败:" + step)

        step = '--step4：两个session分别执行commit expect:成功--'
        self.log.info(step)
        db1_commit_result = self.db1.query('commit;')
        self.log.info(db1_commit_result)
        db2_commit_result = self.db2.query('commit;')
        self.log.info(db2_commit_result)
        self.assertIn('COMMIT', db1_commit_result, "执行失败:" + step)
        self.assertIn('COMMIT', db2_commit_result, "执行失败:" + step)

        step = '--step5：查询数据 expect:查询数据删除成功--'
        self.log.info(step)
        sql_cmd = f'select * from {self.table_name};'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败:" + step)

    def tearDown(self):
        step = '--恢复环境，关闭session，删除测试表--'
        self.log.info(step)
        self.db1.close()
        self.db2.close()
        result = self.primary_sh.execut_db_sql(f'drop table {self.table_name};')
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

